package com.zis.platform.common.mybatis.sqlparser;

import java.util.ArrayList;
import java.util.List;

import org.springframework.util.CollectionUtils;

import com.zis.util.Salt;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.LateralSubSelect;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.select.SubJoin;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.statement.select.ValuesList;
import net.sf.jsqlparser.statement.select.WithItem;

/**
 * 
 * <b>说明：</b>SQL语句处理解析工具类
 * 
 * @ClassName: SqlParserUtil
 * @author zhaohaitao(2543)
 * @date 2015-7-10 上午10:14:11
 * 
 */
public class SqlParserUtil
{
    private static List<String> tableNames;
    
    /**
     * 获取sql语句中所有的表名
     * 
     * @param sql
     * @return
     * @throws JSQLParserException
     */
    public static List<String> getTableNames(String sql)
        throws JSQLParserException
    {
        tableNames = new ArrayList<String>();
        // 解析SQL
        Statement stmt = CCJSqlParserUtil.parse(sql);
        if (stmt instanceof Select)
        {
            Select select = (Select)stmt;
            SelectBody selectBody = select.getSelectBody();
            parseSelectBody(selectBody);
        }
        return tableNames;
    }
    
    /**
     * 查询体 解析
     * 
     * @param selectBody
     */
    private static void parseSelectBody(SelectBody selectBody)
    {
        if (selectBody instanceof PlainSelect)
        {
            PlainSelect plainSelect = (PlainSelect)selectBody;
            parseFromItem(plainSelect.getFromItem());
            parseJoins(plainSelect.getJoins());
        }
        else if (selectBody instanceof SetOperationList)
        {
            SetOperationList optList = (SetOperationList)selectBody;
            List<PlainSelect> plainSelects = optList.getPlainSelects();
            if (!CollectionUtils.isEmpty(plainSelects))
            {
                for (PlainSelect select : plainSelects)
                {
                    parseSelectBody(select);
                }
            }
        }
        else if (selectBody instanceof WithItem)
        {
            WithItem withItem = (WithItem)selectBody;
            parseSelectBody(withItem.getSelectBody());
        }
    }
    
    /**
     * from元素 解析
     * 
     * @param fromItem
     */
    private static void parseFromItem(FromItem fromItem)
    {
        if (fromItem instanceof Table)
        {
            Table table = (Table)fromItem;
            tableNames.add(table.getName().toUpperCase());
        }
        else if (fromItem instanceof LateralSubSelect)
        {
            LateralSubSelect lateralSubSelect = (LateralSubSelect)fromItem;
            SubSelect subSelect = lateralSubSelect.getSubSelect();
            SelectBody selectBody = subSelect.getSelectBody();
            parseSelectBody(selectBody);
        }
        else if (fromItem instanceof SubJoin)
        {
            SubJoin subJoin = (SubJoin)fromItem;
            Join join = subJoin.getJoin();
            FromItem rightItem = join.getRightItem();
            parseFromItem(rightItem);
        }
        else if (fromItem instanceof SubSelect)
        {
            SubSelect subSelect = (SubSelect)fromItem;
            SelectBody selectBody = subSelect.getSelectBody();
            parseSelectBody(selectBody);
        }
        else if (fromItem instanceof ValuesList)
        {
            // 值集合 不做处理
        }
    }
    
    /**
     * 左右外联 解析
     * 
     * @param joins
     * @return
     */
    private static void parseJoins(List<Join> joins)
    {
        if (joins != null)
        {
            for (Join join : joins)
            {
                parseFromItem(join.getRightItem());
            }
        }
    }
    
    /**
     * 根据数据过滤规则 对sql进行格式化 以达到数据过滤的目的
     * 
     * @param sql
     * @param strategy
     * @return
     * @throws JSQLParserException
     */
    public static String changeSql(String sql, String formatSql, String currentTableName)
        throws JSQLParserException
    {
        Statement stmt = CCJSqlParserUtil.parse(sql);
        if (stmt instanceof Select)
        {
            Select select = (Select)stmt;
            SelectBody selectBody = select.getSelectBody();
            changeSelectBody(selectBody, formatSql, currentTableName);
            return select.toString();
        }
        return null;
    }
    
    /**
     * 查询体 解析
     * 
     * @param selectBody
     * @throws JSQLParserException
     */
    private static void changeSelectBody(SelectBody selectBody, String sql, String currentTableName)
        throws JSQLParserException
    {
        if (selectBody instanceof PlainSelect)
        {
            PlainSelect plainSelect = (PlainSelect)selectBody;
            FromItem fromItem = plainSelect.getFromItem();
            if (fromItem instanceof Table)
            {
                Table t = (Table)fromItem;
                if (currentTableName.equals(t.getName()))
                {
                    // 格式化sql
                    SubSelect subSelect = getPlainSelect(sql, t.getAlias());
                    plainSelect.setFromItem(subSelect);
                }
            }
            else
            {
                changeFromItem(fromItem, sql, currentTableName);
            }
            
            changeJoins(plainSelect.getJoins(), sql, currentTableName);
        }
        else if (selectBody instanceof SetOperationList)
        {
            SetOperationList optList = (SetOperationList)selectBody;
            List<PlainSelect> plainSelects = optList.getPlainSelects();
            if (!CollectionUtils.isEmpty(plainSelects))
            {
                for (PlainSelect select : plainSelects)
                {
                    changeSelectBody(select, sql, currentTableName);
                }
            }
        }
        else if (selectBody instanceof WithItem)
        {
            WithItem withItem = (WithItem)selectBody;
            changeSelectBody(withItem.getSelectBody(), sql, currentTableName);
        }
    }
    
    /**
     * from元素 解析
     * 
     * @param fromItem
     * @throws JSQLParserException
     */
    private static void changeFromItem(FromItem fromItem, String sql, String currentTableName)
        throws JSQLParserException
    {
        
        if (fromItem instanceof LateralSubSelect)
        {
            LateralSubSelect lateralSubSelect = (LateralSubSelect)fromItem;
            SubSelect subSelect = lateralSubSelect.getSubSelect();
            SelectBody selectBody = subSelect.getSelectBody();
            changeSelectBody(selectBody, sql, currentTableName);
        }
        else if (fromItem instanceof SubJoin)
        {
            SubJoin subJoin = (SubJoin)fromItem;
            Join join = subJoin.getJoin();
            FromItem rightItem = join.getRightItem();
            changeFromItem(rightItem, sql, currentTableName);
        }
        else if (fromItem instanceof SubSelect)
        {
            SubSelect subSelect = (SubSelect)fromItem;
            SelectBody selectBody = subSelect.getSelectBody();
            changeSelectBody(selectBody, sql, currentTableName);
        }
        else if (fromItem instanceof ValuesList)
        {
            // 值集合 不做处理
        }
    }
    
    /**
     * 左右外联 解析
     * 
     * @param joins
     * @return
     * @throws JSQLParserException
     */
    private static void changeJoins(List<Join> joins, String sql, String currentTableName)
        throws JSQLParserException
    {
        if (joins != null)
        {
            for (Join join : joins)
            {
                FromItem fromItem = join.getRightItem();
                if (fromItem instanceof Table)
                {
                    Table t = (Table)fromItem;
                    if (currentTableName.equals(t.getName()))
                    {
                        // 格式化sql
                        SubSelect subSelect = getPlainSelect(sql, t.getAlias());
                        join.setRightItem(subSelect);
                    }
                }
                else
                {
                    changeFromItem(fromItem, sql, currentTableName);
                }
            }
        }
    }
    
    private static SubSelect getPlainSelect(String sql, Alias alias)
        throws JSQLParserException
    {
        
        if (alias == null)
        {
            alias = new Alias(Salt.genRandomL8());
        }
        Statement stmt = CCJSqlParserUtil.parse(sql);
        if (stmt instanceof Select)
        {
            Select select = (Select)stmt;
            SelectBody selectBody = select.getSelectBody();
            SubSelect subSelect = new SubSelect();
            subSelect.setSelectBody(selectBody);
            subSelect.setAlias(alias);
            return subSelect;
        }
        return null;
    }
}
